##########################################################################
## Replication code for                                                 ##
## War on Aisle 5: Casualties, National Identity, and Consumer Behavior ##
## Benjamin Helms, Sonal S. Pandya, and Rajkumar Venkatesan             ##
## January 19, 2024                                                     ##
## Journal of Conflict Resolution                                       ##
##########################################################################

## This file contains code to clean, process, and merge the Wesleyan Media Project 
## 2006 Congressional advertising data in order to replicate Appendix Table A.21.

## Access to the Wesleyan Media Project data is subject to a data use agreement.
## Visit https://mediaproject.wesleyan.edu/dataaccess/ to access the 2006 
## advertising data file.

## Enter the working directory containing the Wesleyan Media Project data here:
setwd("")

## Load Wesleyan Media Project data.
ad_data <- read_dta("2006_ads.dta")

## Select necessary variables.
ad_data <- select(ad_data,
                  market,
                  dma,
                  l,
                  airdate,
                  categorystate,
                  race,
                  party,
                  issue70)

## Rename variables.
ad_data <- rename(ad_data,
                  length=l,
                  state=categorystate,
                  mention_iraq=issue70)

## Convert party and Iraq mentioned variables to numeric values.
ad_data <- mutate(ad_data,
                  party=as.numeric(party),
                  mention_iraq=as.numeric(mention_iraq))

## Create partisan indicator variables.
ad_data$democrat <- if_else(ad_data$party==1, 1, 0)
ad_data$republican <- if_else(ad_data$party==2, 1, 0)
ad_data$independent <- if_else(ad_data$party==3, 1, 0)

## Remove party variable.
ad_data <- select(ad_data, -party)

## Select and reorder variables.
ad_data <- select(ad_data,
                  market,
                  dma,
                  state,
                  airdate,
                  length,
                  democrat,
                  republican,
                  independent,
                  mention_iraq)

## Create time variables for Iraq-related advertising, both in total
## and by partisan identification.
ad_data <- mutate(ad_data,
                  time_iraq=length*mention_iraq,
                  time_iraq_dem=length*mention_iraq*democrat,
                  time_iraq_rep=length*mention_iraq*republican)

## Select variables.
ad_data <- select(ad_data,
                  dma,
                  airdate,
                  length,
                  time_iraq,
                  time_iraq_dem,
                  time_iraq_rep)

## Convert date variable format.
ad_data$airdate=as.Date(ad_data$airdate,format = "%m/%d/%Y")

## Load IRI week and month dataset.
week_month <- read_csv("weekmonth.csv")

## Keep only weeks and months in 2006.
week_month <- filter(week_month, Year==2006)

## Create date variables for merging purposes.
week_month$startweek=as.Date(week_month$startweek,format = "%m/%d/%Y")
week_month$endweek=as.Date(week_month$endweek,format = "%m/%d/%Y")

## Select only ads that fall within the sample period.
ad_data <-sqldf("select ad_data.*,week_month.IRI_Week from ad_data, week_month where ad_data.airdate >= week_month.startweek and ad_data.airdate <= week_month.endweek")

## Select variables.
ad_data <- select(ad_data,
                  dma,
                  IRI_Week,
                  everything())

## Group data by DMA and week.
ad_data <- group_by(ad_data, dma, IRI_Week)

## Create DMA-level time variables for Iraq-related advertising, both in total
## and by partisan identification.
ad_data <- summarize(ad_data,
                     time_total=sum(length, na.rm = TRUE),
                     time_iraq=sum(time_iraq, na.rm = TRUE),
                     time_iraq_dem=sum(time_iraq_dem, na.rm = TRUE),
                     time_iraq_rep=sum(time_iraq_rep, na.rm = TRUE))

## Ungroup data.
ad_data <- ungroup(ad_data)

## Create DMA-level share of time variables for Iraq-related advertising, both
## in total and by partisan identification.
ad_data <- mutate(ad_data,
                  share_iraq=(time_iraq/time_total)*100,
                  share_iraq_dem=(time_iraq_dem/time_total)*100,
                  share_iraq_rep=(time_iraq_rep/time_total)*100)

## Select variables.
ad_data <- select(ad_data,
                  dma,
                  IRI_Week,
                  time_total,
                  share_iraq,
                  share_iraq_dem,
                  share_iraq_rep)

## Arrange data by DMA and week.
ad_data <- arrange(ad_data, dma, IRI_Week)

## At this stage, the Wesleyan Media Project 2006 Congressional advertising data
## are processed. The code below merges the advertising data with the 2006
## version of the analysis dataset.

## Rename variables for merging.
ad_data <- rename(ad_data,
                  iri_week=IRI_Week,
                  dma_code=dma)

## Merge analysis dataset with the processed advertising data.
data_06 <- left_join(data_06,
                     ad_data)

## Replace missing observations with zero to reflect zero advertising time.
data_06$time_total <- if_else(data_06$dma_code %in% ad_data$dma_code, replace_na(data_06$time_total, 0), data_06$time_total)
data_06$share_iraq <- if_else(data_06$dma_code %in% ad_data$dma_code, replace_na(data_06$share_iraq, 0), data_06$share_iraq)
data_06$share_iraq_dem <- if_else(data_06$dma_code %in% ad_data$dma_code, replace_na(data_06$share_iraq_dem, 0), data_06$share_iraq_dem)
data_06$share_iraq_rep <- if_else(data_06$dma_code %in% ad_data$dma_code, replace_na(data_06$share_iraq_rep, 0), data_06$share_iraq_rep)

## Remove advertising data and IRI week and month dataset.
rm(ad_data,
   week_month)



